var express = require('express');
var Msdb = require("../../../db/Msdb.js");
var router = express.Router();   //可使用 express.Router 类创建模块化、可挂载的路由句柄
const jwt = require('jsonwebtoken');
var Excel = require('exceljs');
var multer = require('multer');
var UUID = require('uuid');
var request = require("request");
const { getxl_util, getnowdate, saveczlog_util, res_util, checkbysql, addformfield, getnowdate_djh, setformfield,
   insert_table, update_table_wstr, update_table_byid, resutdata_to_formdata, resutdata_to_formdata_arr, save_qlczslog_util,
   usequery, 
   getxl_util_free} = require('./server_util.js')

const log = require('../../../logger.js');
const db = require('../../../db/Redisdb.js');
var logger = require('../../../logger.js').logger;

//下推用料需求
router.post('/xt_ylxq', async function (req, res) {
   let {scjhid,storage} = req.body
   let db = new Msdb()
   var scjhData = await usequery(`select * from shipSRM_scjh where id='${scjhid}'`)
   var scjhMxData = await usequery(`select * from shipSRM_scjh_mx where mainid='${scjhid}'`)
   //生成用料需求主表数据
   let ylxqId=await getxl_util()
   let ylxqDjh="YLXQ"+await getxl_util_free('seq_ylxq')
   let ylxqSql = `insert into shipSRM_ylxq (id,create_time,create_by,zdr,djrq,djh,ydbh,ydid,ydlx) 
   values ('${ylxqId}',getdate(),'${storage.ygxm}','${storage.ygxm}',getdate(),'${ylxqDjh}','${scjhData[0].djh}',
   '${scjhData[0].id}','生产计划')`
   await db.update(ylxqSql)
   await saveczlog_util(ylxqSql, storage, '', '下推用料需求', '下推', 'shipSRM_ylxq')
   //生成用料需求明细数据
   for(var i=0;i<=scjhMxData.length-1;i++){
      let mxitem = scjhMxData[i]
      mxitem.kcsl=mxitem.kcsl?mxitem.kcsl:0
      mxitem.aqkc=mxitem.aqkc?mxitem.aqkc:0
      //根据物料编码查询物料清单BOM
      let cpdata = await usequery(`select * from shipSRM_wlqd where cpbm='${mxitem.wlbm}'`)
      console.log(cpdata,"111111111111111111111111111111");
      let ylxqmxId=await getxl_util()
      //如果物料清单BOM存在
      if(cpdata.length>0){
         let cssl = mxitem.jhs - mxitem.kcsl + mxitem.aqkc
         //插入成品明细
         let ylxqmxSql = `insert into shipSRM_ylxq_cpmx (mainid,id,create_time,create_by,wlmc,wlbm,dw,ggxh,bbh,ph,jhsl
         ,kcsl,aqkc,cssl,ydid,ydmxid,yddh,wlqdbh,wlqdid,wlqdzt) 
         values ('${ylxqId}','${ylxqmxId}',getdate(),'${storage.ygxm}','${mxitem.cpmc}','${mxitem.wlbm}',${mxitem.dw},'${mxitem.cpth}','${mxitem.bbh}','000','${mxitem.jhs}'
         ,'${mxitem.kcsl}','${mxitem.aqkc}','${cssl}','${scjhData[0].id}','${mxitem.id}','${scjhData[0].djh}','${cpdata[0].bh}','${cpdata[0].id}','正常')`
         await db.update(ylxqmxSql)
         await saveczlog_util(ylxqmxSql, storage, '', '下推用料需求成品明细', '下推', 'shipSRM_ylxq_cpmx')
         //插入原料明细
         for(var j=0;j<=cpdata.length-1;j++){
            let cpitem = cpdata[j]
            if(cpitem.sfzz=="外购"){
               //根据物料编码查询库存数量
               let rtdata = await usequery(`select a.kcsl,b.aqkc from v_jskcb a left join shipSRM_wlda b on a.wlbm=b.wlbm where a.wlbm='${cpitem.wlbm}'`)
               let ylcssl = cssl * cpitem.sl - rtdata[0].kcsl + rtdata[0].aqkc
               let ylxqylmxId= getxl_util()
               let ylxqylmxSql = `insert into shipSRM_ylxq_ylmx (mainid,id,create_time,create_by,wlmc,wlbm,dw,ggxh,bbh,ph,jhsl
            ,kcsl,aqkc,cssl,ydid,ydmxid,ydbh,wlqdbh,wlqdid,wlqdzt,cpmxid) 
            values ('${ylxqId}','${ylxqylmxId}',getdate(),'${storage.ygxm}','${cpitem.cpmc}','${cpitem.wlbm}',${cpitem.dw},'${cpitem.cpth}','${cpitem.bbh}','000','${mxitem.jhs}'
            ,'${rtdata[0].kcsl}','${rtdata[0].aqkc}','${ylcssl}','${scjhData[0].id}','${mxitem.ydmxid}','${scjhData[0].djh}','${cpdata[0].bh}','${cpdata[0].id}','正常','${ylxqmxId}')`
               await db.update(ylxqylmxSql)
               await saveczlog_util(ylxqylmxSql, storage, '', '下推用料需求原料明细', '下推', 'shipSRM_ylxq_ylmx')
            }
         }
      }
      else{
         let cssl = mxitem.jhs - mxitem.kcsl + mxitem.aqkc
         //插入成品明细
         let ylxqmxSql = `insert into shipSRM_ylxq_cpmx (mainid,id,create_time,create_by,wlmc,wlbm,dw,ggxh,bbh,ph,jhsl
         ,kcsl,aqkc,cssl,ydid,ydmxid,yddh,wlqdbh,wlqdid,wlqdzt) 
         values ('${ylxqId}','${ylxqmxId}',getdate(),'${storage.ygxm}','${mxitem.cpmc}','${mxitem.wlbm}',${mxitem.dw},'${mxitem.cpth}','${mxitem.bbh}','000','${mxitem.jhs}'
         ,'${mxitem.kcsl}','','${cssl}','${scjhData[0].id}','${mxitem.id}','${scjhData[0].djh}','','','无物料清单BOM')`
         await db.update(ylxqmxSql)
         await saveczlog_util(ylxqmxSql, storage, '', '下推用料需求成品明细无BOM', '下推', 'shipSRM_ylxq_cpmx')
      }
   }
   res_util(res, {}, true, "查询成功")
})


//查询用料需求列表
router.post('/ylxqlb', async function (req, res) {
   let {storage } = req.body
   var rtdata = await usequery(`select * from shipSRM_ylxq order by create_time desc`)
   res_util(res, rtdata, true, "查询成功")
})

//查询用料需求列表通过id
router.post('/query_ylxq_byid', async function (req, res) {
   var data = await usequery(`select * from shipSRM_ylxq where id='${req.body.data.id}' order by create_time desc`)
   var cpmxdata = await usequery(`select b.* from shipSRM_ylxq a,shipSRM_ylxq_cpmx b where a.id=b.mainid `)
   var ylmxdata = await usequery(`select b.* from shipSRM_ylxq a,shipSRM_ylxq_ylmx b where a.id=b.mainid `)
   res_util(res, { formdata: data[0],cpmxdata,ylmxdata}, true, "查询成功")
})

//新增用料需求
router.post('/add_ylxq', async function (req, res) {
   let { formdata, mxdata, storage } = req.body
   var insrtobj = await insert_table(formdata, "shipSRM_ylxq")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增用料需求', '新增', 'shipSRM_ylxq')
   formdata = insrtobj.data;
   var mainid = insrtobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = await insert_table(mxitem, "shipSRM_ylxq_tzbgmx")
      await saveczlog_util(rtmxobj.insertsql, storage, '', '新增用料需求明细', '新增', 'shipSRM_ylxq_tzbgmx')                     
   }
   var rtmxdata = await usequery(`select top 1 *  from shipSRM_ylxq_tzbgmx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, `新增用料需求成功`)
})

//更新用料需求
router.post('/update_ylxq', async function (req, res) {
   let db=new Msdb()
   let { formdata, mxdata, storage, delidarr } = req.body
   let updatobj = await update_table_byid(formdata, "shipSRM_ylxq")
   await saveczlog_util(updatobj.updatesql, storage, '', '修改用料需求', '修改', 'shipSRM_ylxq')
   formdata = updatobj.data;
   var mainid = updatobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      var ifcz = await checkbysql(`select * from shipSRM_ylxq_tzbgmx where id='${mxitem.id.value}'`)
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = {}
      if (!ifcz) {
         rtmxobj = await insert_table(mxitem, "shipSRM_ylxq_tzbgmx")
      } else {
         rtmxobj = await update_table_byid(mxitem, "shipSRM_ylxq_tzbgmx")
      }
   }
   
   //删除delidarr
   await db.update(`delete from shipSRM_ylxq_tzbgmx where id in (${delidarr.map(item => `'${item}'`).join(',')}) `)
   var rtmxdata = await usequery(`select *  from shipSRM_ylxq_tzbgmx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, "更新用料需求成功")
})

//  删除用料需求
router.post('/delete_ylxq', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `delete from shipSRM_ylxq where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await db.update(`delete from shipSRM_ylxq_tzbgmx where mainid in (${selectedRows.map(item => `'${item.id}'`).join(',')})`)
   await saveczlog_util(delsql, storage, '', '删除用料需求', '删除', 'shipSRM_ylxq')
   res_util(res, {}, true, "删除成功")
})


const excel_export_scjh = async (id)=>{
   var start_time = new Date();
   const filenamettt = `./dist2/oaexcelfile/生产计划.xlsx`;
 
   //step0 先获取计划数据
   const edata=await usequery(`select convert(varchar,a.create_time,120) djrq,a.djh,b.*
      from shipSRM_scjh  a,shipSRM_scjh_mx b
       where a.id=b.mainid and a.id='${id}' order by case when b.cj='水箱车间' then 1 when b.cj='管件车间' then 2 when b.cj='尿素车间' then 3 else 4 end, kh`)
      
 
       let maindata=edata[0]
       let mxdatalist=edata
  
  
       let processedData = [];
       let previousCj = null;
       let seenKhs = new Set();
       let cjCounts = {}
       
       mxdatalist.forEach((item, index) => {
         if (previousCj !== null && item.cj !== previousCj) {
           processedData.push({ text: `以上为 ${previousCj}生产计划`,lx:"oneline" });
           processedData.push({ text: ``,lx:"oneline" });
  
         }
         if (seenKhs.has(item.kh)) {
            item.kh = ''
          } else {
            seenKhs.add(item.kh)
          }
    
          if (cjCounts[item.cj]) {
            cjCounts[item.cj]++;
          } else {
            cjCounts[item.cj] = 1;
          }
          if (item.kh === '') {
            item.mergeKh = true;
          } else {
            item.mergeKh = false;
          }
         processedData.push(item);
         previousCj = item.cj;
         if (index === mxdatalist.length - 1) {
           processedData.push({ text: `以上为 ${item.cj}生产计划`,lx:"oneline" });
           processedData.push({ text: ``,lx:"oneline" });
  
         }
       });
  
  
       //EXCE
  
  
  var workbook = new Excel.stream.xlsx.WorkbookWriter({
    filename: filenamettt,
    useStyles: true,
  });
  
  
       var worksheet = workbook.addWorksheet('Sheet');
  
    //step1 计算表格范围 画边框线
  
    //C列和F列宽度设置宽一点
    worksheet.getColumn('A').width = 3;
    worksheet.getColumn('B').width = 24;
    worksheet.getColumn('C').width = 20;
    worksheet.getColumn('D').width = 6;
    worksheet.getColumn('E').width = 14;
    worksheet.getColumn('F').width = 10;
    worksheet.getColumn('G').width = 10;
    worksheet.getColumn('H').width = 8;
    worksheet.getColumn('I').width = 12;
    //DEF列设置垂直居中
   worksheet.getColumn('D').alignment = { vertical: 'middle', horizontal: 'center' };
   worksheet.getColumn('E').alignment = { vertical: 'middle' };
   worksheet.getColumn('F').alignment = { vertical: 'middle' };
   worksheet.getColumn('I').alignment = { vertical: 'middle', horizontal: 'center' };
   worksheet.getColumn('A').alignment = { vertical: 'middle', horizontal: 'center' };
  
    //
  
    //设置12345行高为20
    worksheet.getRow(1).height = 20;
    worksheet.getRow(2).height = 20;
    worksheet.getRow(3).height = 20;
    worksheet.getRow(4).height = 20;
    worksheet.getRow(5).height = 20;
    //4，5行设置垂直居中
    worksheet.getRow(4).alignment = { vertical: 'middle' };
    worksheet.getRow(5).alignment = { vertical: 'middle' };
  
    // 设置一个单元格范围（例如A1到C3）内外边框为实线
  const startCell = worksheet.getCell('A1');
  const endCell = worksheet.getCell('I40');
  
  // 设置内部边框为实线
     for (let row = startCell.row; row <= endCell.row; row++) {
       for (let col = startCell.col; col <= endCell.col; col++) {
         const cell = worksheet.getCell(row, col);
         cell.border = {
           top: { style: 'thin' },
           left: { style: 'thin' },
           bottom: { style: 'thin' },
           right: { style: 'thin' }
         };
         cell.font = {
           name: '黑体', // 设置字体为黑体
         };
  
       }
     }
  //明细列头
    worksheet.mergeCells(`A1:B3`);
    worksheet.mergeCells(`C1:I2`);
    worksheet.mergeCells(`D3:F3`);
    worksheet.mergeCells(`A4:A5`);
    worksheet.mergeCells(`B4:B5`);
  
    worksheet.getCell(`D3`).value=maindata.djrq.substring(0,10)
  
    worksheet.getCell('A1').alignment = { vertical: 'middle', horizontal: 'center' };
    worksheet.getCell('A1').font = { size: 24, bold: true };
    worksheet.getCell(`A1`).value = "NCH";
    worksheet.getCell('C1').alignment = { vertical: 'middle', horizontal: 'center' };
    worksheet.getCell('C1').font = { size: 20, bold: true };
    worksheet.getCell(`C1`).value = "生产计划单";
  
    worksheet.getCell(`A4`).value = "序号";
    worksheet.getCell('A4').alignment = { vertical: 'middle', horizontal: 'center' };
  
    worksheet.getCell('A4').alignment = { vertical: 'middle', horizontal: 'center', wrapText: true };
    worksheet.getCell('A4').font = { size: 12 };
  
    worksheet.getCell(`B4`).value = "产品名称";
    worksheet.mergeCells(`C4:C5`);
    worksheet.getCell(`C4`).value = "产品图号";
    worksheet.getCell(`D4`).value = "计划数";
    worksheet.mergeCells(`D4:D5`);
    worksheet.getCell(`E4`).value = "备注";
    worksheet.mergeCells(`E4:E5`);
    worksheet.getCell(`F4`).value = "日期";
    worksheet.getCell(`F5`).value = "计划进度";
    worksheet.getCell(`G4`).value = "状态记录";
    worksheet.getCell(`G5`).value = "计划日期";
    worksheet.getCell(`H4`).value = "实际";
    worksheet.getCell(`H5`).value = "完工数";
    worksheet.getCell(`I4`).value = "客户";
    //当前在第六行
    let currow=6
    for(var i=0;i<=processedData.length-1;i++){
  if(processedData[i].lx=="oneline"){
    worksheet.getRow(6+i).height = 20;
    worksheet.mergeCells(`B${6+i}:C${6+i}`);
    worksheet.getCell(`B${6+i}`).value = processedData[i].text;
    //再加粗放大一号
  
  }
  
  else{
    //设置行高
    worksheet.getRow(6+i).height = 20;
    if (i > cjCounts['水箱车间']) {
      worksheet.getCell(`A${6 + i}`).value = i - 1;
    } else if (i > cjCounts['管件车间'] + cjCounts['水箱车间']) {
      worksheet.getCell(`A${6 + i}`).value = i - 3;
    } else {
      worksheet.getCell(`A${6 + i}`).value = i + 1;
    }
      worksheet.getCell(`A${6+i}`).value = i+1;
      worksheet.getCell(`B${6+i}`).value = processedData[i].cpmc;
      worksheet.getCell(`C${6+i}`).value = processedData[i].cpth;
      worksheet.getCell(`D${6+i}`).value = processedData[i].jhs;
      worksheet.getCell(`E${6+i}`).value = processedData[i].bz;
      worksheet.getCell(`F${6+i}`).value = new Date(processedData[i].jhrq).getMonth() + 1 + '月' + new Date(processedData[i].jhrq).getDate() + '日';
      worksheet.getCell(`I${6+i}`).value = processedData[i].kh;
    }
   }
   let mergeRanges = [];

   for (let i = 0; i < processedData.length; i++) {
     if (processedData[i].mergeKh) {
       let start = 6 + i;
       while (i < processedData.length - 1 && processedData[i + 1].mergeKh) {
         i++;
       }
       let end = 6 + i;
       mergeRanges.push({ start: { row: start-1, column: 9 }, end: { row: end, column: 9 } });
     }
   }
   
   // 设置合并单元格
   mergeRanges.forEach(merge => {
     try {
       worksheet.mergeCells(merge.start.row, merge.start.column, merge.end.row, merge.end.column);
     } catch (error) {
       console.error(`Error merging cells from row ${merge.start.row} to row ${merge.end.row}:`, error.message);
     }
   });
  
    //当前位置为6+processedData.length
    currow=7+processedData.length
  
    worksheet.mergeCells(`A${currow}:E${currow}`);
    worksheet.getCell(`A${currow}`).value="编制：胡亚飞"
      worksheet.getCell(`F${currow}`).value="计划"
      worksheet.getCell(`H${currow}`).value="日期："
  
  
    worksheet.mergeCells(`A${currow+1}:E${currow+1}`);
      worksheet.getCell(`A${currow+1}`).value="呈报：董事长、总经理"
      worksheet.getCell(`F${currow+1}`).value="批准："
      worksheet.getCell(`H${currow+1}`).value="日期："
  
    worksheet.mergeCells(`A${currow+2}:E${currow+2}`);
    worksheet.getCell(`A${currow+2}`).value="主送：质量保证部、采购部、各车间班组"
    worksheet.mergeCells(`F${currow+2}:G${currow+2}`);
    worksheet.getCell(`F${currow+2}`).value="责任人：各车间班组"
    worksheet.getCell(`H${currow+2}`).value="日期："
  
    worksheet.getCell(`I${currow}`).value=maindata.djrq.substring(0,10)
  
  
      // 冻结至第五行
      worksheet.views = [
        { state: 'frozen', xSplit: 0, ySplit: 4, topLeftCell: 'A5', activeCell: 'A5' }
      ];
   
   //FINAL
   worksheet.commit();
   workbook.commit();
 
 }

  //查询半成品标签是否报工
  router.post('/queryBcpGsbqBind', async function (req, res) {
   let {barcode} =req.body
   var rtdata = await usequery(`select * from shipSRM_gsbq where barcode =${barcode}`)
   if(rtdata.length>0){
       if(rtdata[0].bcpbgr || rtdata[0].bcpsj){
          res_util(res, {}, false, `该标签${barcode}已经报工过了`)
          return
       }
   }
   else{
      res_util(res, {}, false, `没有该标签${barcode}`)
      return
   }
   res_util(res, rtdata, true, "查询成功")
})

 //半成品标签报工
 router.post('/bindBcpGsbq', async function (req, res) {
   let db=new Msdb()
   let {mxdata,storage} =req.body
   for(var i=0;i<=mxdata.length-1;i++){
      let mxitem = mxdata[i]
      let rtdata = await usequery(`select * from shipSRM_gsbq where barcode='${mxitem.barcode}'`)
      if(rtdata.length>0){
         if(rtdata[0].bcpbgr || rtdata[0].bcpsj){
            res_util(res, {}, false, `该标签${mxitem.barcode}已经报工过了`)
            return
         }
      }
      else{
         res_util(res, {}, false, `没有该标签${mxitem.barcode}`)
         return
      }
   }
   for(var i=0;i<=mxdata.length-1;i++){
      let mxitem = mxdata[i]
      let sql = `update shipSRM_gsbq set bcpbgr='${storage.ygxm}',bcpsj=getdate(),bcpbgsm='${mxitem.bz}',dqzt='半成品已报工' where barcode='${mxitem.barcode}'`
      await db.update(sql)
      await saveczlog_util(sql, storage, '', '半成品标签报工', '报工', 'shipSRM_gsbq')
   }
   res_util(res, {}, true, "报工成功")
})

 //查询标签是否存在
 router.post('/queryGsbqBind', async function (req, res) {
   let {barcode} =req.body
   var rtdata = await usequery(`select * from shipSRM_gsbq where barcode =${barcode}`)
   if(rtdata.length>0){
       if(rtdata[0].bdr || rtdata[0].bdsj){
          res_util(res, {}, false, `该标签${barcode}已经绑定过了`)
          return
       }
   }
   else{
      res_util(res, {}, false, `没有该标签${barcode}`)
      return
   }
   res_util(res, rtdata, true, "查询成功")
})

 //绑定标签
 router.post('/bindGsbq', async function (req, res) {
   let db=new Msdb()
   let {mxdata,storage} =req.body
   for(var i=0;i<=mxdata.length-1;i++){
      let mxitem = mxdata[i]
      let rtdata = await usequery(`select * from shipSRM_gsbq where barcode='${mxitem.barcode}'`)
      if(rtdata.length>0){
         if(rtdata[0].bdr || rtdata[0].bdsj){
            res_util(res, {}, false, `该标签${mxitem.barcode}已经绑定过了`)
            return
         }
      }
      else{
         res_util(res, {}, false, `没有该标签${mxitem.barcode}`)
         return
      }
   }
   for(var i=0;i<=mxdata.length-1;i++){
      let mxitem = mxdata[i]
      let sql = `update shipSRM_gsbq set bdr='${storage.ygxm}',bdsj=getdate(),bdsm='${mxitem.bz}',dqzt='标签已绑定' where barcode='${mxitem.barcode}'`
      await db.update(sql)
      await saveczlog_util(sql, storage, '', '绑定滚塑标签', '绑定', 'shipSRM_gsbq')
   }
   res_util(res, {}, true, "绑定成功")
})
 
 //查询滚塑标签列表
router.post('/Gsbq_list', async function (req, res) {
   var rtdata = await usequery(`select * from shipSRM_gsbq order by create_time desc`)
   res_util(res, rtdata, true, "查询成功")
})

//导出生产计划
router.post('/scjh_excel', async function (req, res) {
var {selectedRows}=req.body

excel_export_scjh(selectedRows[0].id)

   res.send(JSON.stringify({
      url: "http://111.77.154.58:2233/oaexcelfile/生产计划.xlsx"
   }));
})

//查询生产订单列表
router.post('/scdd_list', async function (req, res) {
   var rtdata = await usequery(`select * from shipSRM_scdd order by create_time desc`)
   res_util(res, rtdata, true, "查询成功")
})

//查询生产订单列表通过id
router.post('/query_scdd_byid', async function (req, res) {
   var id = req.body.id
   var formdata = await usequery(`select *  from shipSRM_scdd where id ='${id}'`)
   var mxdata = await usequery(`select *  from shipSRM_scdd_mx where mainid ='${id}'`)
   res_util(res, { formdata:formdata[0], mxdata }, true, "查询成功")
})

//新增生产订单
router.post('/add_scdd', async function (req, res) {
   let { formdata, mxdata, storage } = req.body
   let xlm = await getxl_util_free("seq_scdd")
   formdata.djh.value="SGDD"+xlm
   var insrtobj = await insert_table(formdata, "shipSRM_scdd")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增生产订单', '新增', 'shipSRM_scdd')
   formdata = insrtobj.data;
   var mainid = insrtobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = await insert_table(mxitem, "shipSRM_scdd_mx")
      await saveczlog_util(rtmxobj.insertsql, storage, '', '新增生产订单明细', '新增', 'shipSRM_scdd_mx')                     
   }
   var rtmxdata = await usequery(`select top 1 *  from shipSRM_scdd_mx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, `新增生产订单成功,生产订单单据号${formdata.djh.value}`)
})

//更新生产订单
router.post('/update_scdd', async function (req, res) {
   let db=new Msdb()
   let { formdata, mxdata, storage, delidarr } = req.body
   let updatobj = await update_table_byid(formdata, "shipSRM_scdd")
   await saveczlog_util(updatobj.updatesql, storage, '', '修改生产订单', '修改', 'shipSRM_scdd')
   formdata = updatobj.data;
   var mainid = updatobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      var ifcz = await checkbysql(`select * from shipSRM_scdd_mx where id='${mxitem.id.value}'`)
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = {}
      if (!ifcz) {
         rtmxobj = await insert_table(mxitem, "shipSRM_scdd_mx")
      } else {
         rtmxobj = await update_table_byid(mxitem, "shipSRM_scdd_mx")
      }
   }

   //删除delidarr
   await db.update(`delete from shipSRM_scdd_mx where id in (${delidarr.map(item => `'${item}'`).join(',')}) `)
   var rtmxdata = await usequery(`select *  from shipSRM_scdd_mx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, "更新生产订单成功")
})

//  删除生产订单
router.post('/delete_scdd', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `delete from shipSRM_scdd where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await db.update(`delete from shipSRM_scdd_mx where mainid in (${selectedRows.map(item => `'${item.id}'`).join(',')})`)
   await saveczlog_util(delsql, storage, '', '删除生产订单', '删除', 'shipSRM_scdd')
   res_util(res, {}, true, "删除成功")
})





//查询工单列表
router.post('/gd_list', async function (req, res) {
   var rtdata = await usequery(`select * from shipSRM_gd order by create_time desc`)
   res_util(res, rtdata, true, "查询成功")
})

//查询工单列表通过id
router.post('/query_gd_byid', async function (req, res) {
   var id = req.body.id
   var formdata = await usequery(`select *  from shipSRM_gd where id ='${id}'`)
   var mxdata = await usequery(`select *  from shipSRM_gd_mx where mainid ='${id}'`)
   res_util(res, { formdata:formdata[0], mxdata }, true, "查询成功")
})

//新增工单
router.post('/add_gd', async function (req, res) {
   let { formdata, mxdata, storage } = req.body
   let xlm = await getxl_util_free("seq_gd")
   formdata.djh.value="GD"+xlm
   var insrtobj = await insert_table(formdata, "shipSRM_gd")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增工单', '新增', 'shipSRM_gd')
   formdata = insrtobj.data;
   var mainid = insrtobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = await insert_table(mxitem, "shipSRM_gd_mx")
      await saveczlog_util(rtmxobj.insertsql, storage, '', '新增工单明细', '新增', 'shipSRM_gd_mx')                     
   }
   var rtmxdata = await usequery(`select top 1 *  from shipSRM_gd_mx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, `新增工单成功,工单单据号${formdata.djh.value}`)
})

//更新工单
router.post('/update_gd', async function (req, res) {
   let db=new Msdb()
   let { formdata, mxdata, storage, delidarr } = req.body
   let updatobj = await update_table_byid(formdata, "shipSRM_gd")
   await saveczlog_util(updatobj.updatesql, storage, '', '修改工单', '修改', 'shipSRM_gd')
   formdata = updatobj.data;
   var mainid = updatobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      var ifcz = await checkbysql(`select * from shipSRM_gd_mx where id='${mxitem.id.value}'`)
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = {}
      if (!ifcz) {
         rtmxobj = await insert_table(mxitem, "shipSRM_gd_mx")
      } else {
         rtmxobj = await update_table_byid(mxitem, "shipSRM_gd_mx")
      }
   }

   //删除delidarr
   await db.update(`delete from shipSRM_gd_mx where id in (${delidarr.map(item => `'${item}'`).join(',')}) `)
   var rtmxdata = await usequery(`select *  from shipSRM_gd_mx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, "更新工单成功")
})

//  删除工单
router.post('/delete_gd', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `delete from shipSRM_gd where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await db.update(`delete from shipSRM_gd_mx where mainid in (${selectedRows.map(item => `'${item.id}'`).join(',')})`)
   await saveczlog_util(delsql, storage, '', '删除工单', '删除', 'shipSRM_gd')
   res_util(res, {}, true, "删除成功")
})


router.post('/delete_Gsbq', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `delete from shipSRM_gsbq where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await saveczlog_util(delsql, storage, '', '删除滚塑标签', '删除', 'shipSRM_gsbq')
   res_util(res, {}, true, "删除成功")
})

//创建滚塑标签
router.post('/create_Gsbq', async function (req, res) {
   let { formdata, storage } = req.body
   var db=new Msdb()
   for(var i=0;i<formdata.dyzs.value;i++){
      var xlm = await getxl_util()
      //拼上年月日 YYYYMMDD
      var nowdate = new Date().toISOString().split('T')[0].replace(/-/g, '');
      xlm = nowdate+xlm
      formdata.barcode.value = xlm
      var insrtobj = await insert_table(formdata, "shipSRM_gsbq")
      await saveczlog_util(insrtobj.insertsql, storage, '', '创建滚塑标签', '新增', 'shipSRM_gsbq')
   }



   res_util(res, {}, true, "创建成功")
})


//更新滚塑标签打印信息
router.post('/update_printinfo_Gsbq', async function (req, res) {
   let { selectedRows } = req.body
   var db=new Msdb()
   await db.update(`update shipSRM_gsbq set
       sfdy='是',dycs=dycs+1
       where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`)
   res_util(res, {}, true, "更新成功")
})

router.post('/xsdd_list', async function (req, res) {
   let { type, startDataStrref, endDataStrref } = req.body
   var querysql = ``
   type == 'mx' ? querysql = `
      SELECT TOP 1000
 a.id,a.create_time,a.create_by,a.ghdw,a.jhdd,a.ghdwid,a.djh ydh,a.lxr,a.dh,a.sfxt,
 a.djzt,
     CASE WHEN ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY b.id) = 1 
          THEN a.djh ELSE NULL END AS djh,
     b.cpmc, b.cpbm, b.ggxh, b.khdd, b.sl,b.jhrq,b.glsl,b.cksl,b.id mxid,b.dw,b.bbh,b.fhsl,b.xtzt
   FROM shipSRM_xsdd a
   INNER JOIN shipSRM_xsdd_mx b ON b.mainid = a.id
   where 1=1
 `: querysql = `SELECT TOP 1000 * FROM shipSRM_xsdd WHERE 1=1`
 
 if (startDataStrref || endDataStrref) {
   querysql = querysql.replace('TOP 1000', '')
   if (type === "mx") {
     if (startDataStrref && endDataStrref) {
         querysql += ` AND a.create_time BETWEEN '${startDataStrref}' AND '${endDataStrref}' `;
     } else if (startDataStrref) {
         querysql += ` AND a.create_time > '${startDataStrref}' `;
     } else if (endDataStrref) {
         querysql += ` AND a.create_time < '${endDataStrref}' `;
     }
   } else {
     if (startDataStrref && endDataStrref) {
         querysql += ` AND create_time BETWEEN '${startDataStrref}' AND '${endDataStrref}' `;
     } else if (startDataStrref) {
         querysql += ` AND create_time > '${startDataStrref}' `;
     } else if (endDataStrref) {
         querysql += ` AND create_time < '${endDataStrref}' `;
     }
   }
 }

if(type==="mx"){
   querysql += ` ORDER BY a.id DESC, b.id `
}else{
   querysql += ` ORDER BY id desc `
}
   var rtdata = await usequery(querysql)
   res_util(res, rtdata, true, "查询成功")
})

//查询生产领料单列表
router.post('/sclld_list', async function (req, res) {
   let { type, startDataStrref, endDataStrref } = req.body
   var querysql = ``
   type == 'mx' ? querysql = `
      SELECT TOP 1000
 a.id,a.create_time,a.create_by,a.djh,a.djzt,a.djrq,a.llr,a.sqbm,a.llrid,a.djlx,a.bgr,
     CASE WHEN ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY b.id) = 1 
          THEN a.djh ELSE NULL END AS djh_new,
     b.wlmc, b.wlbm, b.ggxh, b.jdbm,b.dw, b.sl, b.id mxid
   FROM shipSRM_sclld a
   INNER JOIN shipSRM_sclld_mx b ON b.mainid = a.id
   where 1=1
 `: querysql = `SELECT TOP 1000 * FROM shipSRM_sclld WHERE 1=1`
 
 if (startDataStrref || endDataStrref) {
   querysql = querysql.replace('TOP 1000', '')
   if (type === "mx") {
     if (startDataStrref && endDataStrref) {
         querysql += ` AND a.create_time BETWEEN '${startDataStrref}' AND '${endDataStrref}' `;
     } else if (startDataStrref) {
         querysql += ` AND a.create_time > '${startDataStrref}' `;
     } else if (endDataStrref) {
         querysql += ` AND a.create_time < '${endDataStrref}' `;
     }
   } else {
     if (startDataStrref && endDataStrref) {
         querysql += ` AND create_time BETWEEN '${startDataStrref}' AND '${endDataStrref}' `;
     } else if (startDataStrref) {
         querysql += ` AND create_time > '${startDataStrref}' `;
     } else if (endDataStrref) {
         querysql += ` AND create_time < '${endDataStrref}' `;
     }
   }
 }

if(type==="mx"){
   querysql += ` ORDER BY a.djrq DESC, b.id `
}else{
   querysql += ` ORDER BY djrq desc `
}
   var rtdata = await usequery(querysql)
   res_util(res, rtdata, true, "查询成功")
})

//查询生产领料单列表通过id
router.post('/query_sclld_byid', async function (req, res) {
   var id = req.body.id
   var formdata = await usequery(`select *  from shipSRM_sclld where id ='${id}'`)
   var mxdata = await usequery(`select *  from shipSRM_sclld_mx where mainid ='${id}'`)
   res_util(res, { formdata:formdata[0], mxdata }, true, "查询成功")
})

//新增生产领料单
router.post('/add_sclld', async function (req, res) {
   let { formdata, mxdata, storage } = req.body
   let xlm = await getxl_util_free("seq_sclld")
   formdata.djh.value="SCLLD"+xlm
   var insrtobj = await insert_table(formdata, "shipSRM_sclld")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增生产领料单', '新增', 'shipSRM_sclld')
   formdata = insrtobj.data;
   var mainid = insrtobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = await insert_table(mxitem, "shipSRM_sclld_mx")
      await saveczlog_util(rtmxobj.insertsql, storage, '', '新增生产领料单明细', '新增', 'shipSRM_sclld_mx')                     
   }
   var rtmxdata = await usequery(`select top 1 *  from shipSRM_sclld_mx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, `新增生产领料单成功,生产领料单单据号${formdata.djh.value}`)
})

//更新生产领料单
router.post('/update_sclld', async function (req, res) {
   let db=new Msdb()
   let { formdata, mxdata, storage, delidarr } = req.body
   let updatobj = await update_table_byid(formdata, "shipSRM_sclld")
   await saveczlog_util(updatobj.updatesql, storage, '', '修改生产领料单', '修改', 'shipSRM_sclld')
   formdata = updatobj.data;
   var mainid = updatobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      var ifcz = await checkbysql(`select * from shipSRM_sclld_mx where id='${mxitem.id.value}'`)
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = {}
      if (!ifcz) {
         rtmxobj = await insert_table(mxitem, "shipSRM_sclld_mx")
      } else {
         rtmxobj = await update_table_byid(mxitem, "shipSRM_sclld_mx")
      }
   }

   //删除delidarr
   await db.update(`delete from shipSRM_sclld_mx where id in (${delidarr.map(item => `'${item}'`).join(',')}) `)
   var rtmxdata = await usequery(`select *  from shipSRM_sclld_mx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, "更新生产领料单成功")
})

//  删除生产领料单
router.post('/delete_sclld', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `delete from shipSRM_sclld where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await db.update(`delete from shipSRM_sclld_mx where mainid in (${selectedRows.map(item => `'${item.id}'`).join(',')})`)
   await saveczlog_util(delsql, storage, '', '删除生产领料单', '删除', 'shipSRM_sclld')
   res_util(res, {}, true, "删除成功")
})






//查询生产入库单列表
router.post('/scrkd_list', async function (req, res) {
   let {  startDataStrref, endDataStrref, type,} = req.body
   var querysql = ``
   type == 'mx' ? querysql = `
      SELECT TOP 1000 a.id,a.djh AS ydh,a.rkr,a.rkzsl, a.djzt,a.sqbm,a.djrq,
    CASE WHEN ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY b.id) = 1 
         THEN a.djh ELSE NULL END AS djh,
    CASE WHEN ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY b.id) = 1 
         THEN a.create_by ELSE NULL END AS create_by,
    CASE WHEN ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY b.id) = 1 
         THEN a.create_time ELSE NULL END AS create_time,
    b.dj,b.ph,b.sl, b.wlmc, b.wlbm,b.ggxh, b.id AS mxid,b.zldj,b.bbh, b.ck,b.dw
		 FROM shipSRM_scrkd a
    INNER JOIN shipSRM_scrkd_mx b ON b.mainid = a.id
    where 1=1
 `: querysql = `SELECT TOP 1000 * FROM shipSRM_scrkd WHERE 1=1`
 
 if (startDataStrref || endDataStrref) {
   querysql = querysql.replace('TOP 1000', '')
   if (type === "mx") {
     if (startDataStrref && endDataStrref) {
         querysql += ` AND a.create_time BETWEEN '${startDataStrref}' AND '${endDataStrref}' `;
     } else if (startDataStrref) {
         querysql += ` AND a.create_time > '${startDataStrref}' `;
     } else if (endDataStrref) {
         querysql += ` AND a.create_time < '${endDataStrref}' `;
     }
   } else {
     if (startDataStrref && endDataStrref) {
         querysql += ` AND create_time BETWEEN '${startDataStrref}' AND '${endDataStrref}' `;
     } else if (startDataStrref) {
         querysql += ` AND create_time > '${startDataStrref}' `;
     } else if (endDataStrref) {
         querysql += ` AND create_time < '${endDataStrref}' `;
     }
   }
 }

if(type==="mx"){
   querysql += ` ORDER BY a.create_time DESC, b.id `
}else{
   querysql += ` ORDER BY create_time desc `
}
   var rtdata = await usequery(querysql)
   res_util(res, rtdata, true, "查询成功")
})

//查询生产入库单列表通过id
router.post('/query_scrkd_byid', async function (req, res) {
   var id = req.body.id
   var formdata = await usequery(`select *  from shipSRM_scrkd where id ='${id}'`)
   var mxdata = await usequery(`select *  from shipSRM_scrkd_mx where mainid ='${id}'`)
   res_util(res, { formdata:formdata[0], mxdata }, true, "查询成功")
})

//新增生产入库单
router.post('/add_scrkd', async function (req, res) {
   let { formdata, mxdata, storage } = req.body
   let xlm = await getxl_util_free("seq_scrkd")
   formdata.djh.value="SCRKD"+xlm
   var insrtobj = await insert_table(formdata, "shipSRM_scrkd")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增生产入库单', '新增', 'shipSRM_scrkd')
   formdata = insrtobj.data;
   var mainid = insrtobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = await insert_table(mxitem, "shipSRM_scrkd_mx")
      await saveczlog_util(rtmxobj.insertsql, storage, '', '新增生产入库单明细', '新增', 'shipSRM_scrkd_mx')                     
   }
   var rtmxdata = await usequery(`select top 1 *  from shipSRM_scrkd_mx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, `新增生产入库单成功,生产入库单单据号${formdata.djh.value}`)
})

//更新生产入库单
router.post('/update_scrkd', async function (req, res) {
   let db=new Msdb()
   let { formdata, mxdata, storage, delidarr } = req.body
   let updatobj = await update_table_byid(formdata, "shipSRM_scrkd")
   await saveczlog_util(updatobj.updatesql, storage, '', '修改生产入库单', '修改', 'shipSRM_scrkd')
   formdata = updatobj.data;
   var mainid = updatobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      var ifcz = await checkbysql(`select * from shipSRM_scrkd_mx where id='${mxitem.id.value}'`)
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = {}
      if (!ifcz) {
         rtmxobj = await insert_table(mxitem, "shipSRM_scrkd_mx")
      } else {
         rtmxobj = await update_table_byid(mxitem, "shipSRM_scrkd_mx")
      }
   }

   //删除delidarr
   await db.update(`delete from shipSRM_scrkd_mx where id in (${delidarr.map(item => `'${item}'`).join(',')}) `)
   var rtmxdata = await usequery(`select *  from shipSRM_scrkd_mx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, "更新生产入库单成功")
})

//  删除生产入库单
router.post('/delete_scrkd', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `delete from shipSRM_scrkd where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await db.update(`delete from shipSRM_scrkd_mx where mainid in (${selectedRows.map(item => `'${item.id}'`).join(',')})`)
   await saveczlog_util(delsql, storage, '', '删除生产入库单', '删除', 'shipSRM_scrkd')
   res_util(res, {}, true, "删除成功")
})


//查询生产计划列表
router.post('/scjh_mx_list', async function (req, res) {
   let mainid=req.body.mainid
   var rtdata = await usequery(`select * from shipSRM_scjh_mx where mainid='${mainid}' order by cj`)
   res_util(res, rtdata, true, "查询成功")
})

//查询生产计划列表
router.post('/scjh_list', async function (req, res) {
   var rtdata = await usequery(`select * from shipSRM_scjh order by create_time desc`)
   res_util(res, rtdata, true, "查询成功")
})

//查询生产计划列表通过id
router.post('/query_scjh_byid', async function (req, res) {
   var id = req.body.id
   var formdata = await usequery(`select *  from shipSRM_scjh where id ='${id}'`)
   var mxdata = await usequery(`select *  from shipSRM_scjh_mx where mainid ='${id}'`)
   res_util(res, { formdata:formdata[0], mxdata }, true, "查询成功")
})

//新增生产计划
router.post('/add_scjh', async function (req, res) {
   let { formdata, mxdata, storage } = req.body
   let xlm = await getxl_util_free("seq_scjh")
   formdata.djh.value="SCJH"+xlm
   var insrtobj = await insert_table(formdata, "shipSRM_scjh")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增生产计划', '新增', 'shipSRM_scjh')
   formdata = insrtobj.data;
   var mainid = insrtobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = await insert_table(mxitem, "shipSRM_scjh_mx")
      await saveczlog_util(rtmxobj.insertsql, storage, '', '新增生产计划明细', '新增', 'shipSRM_scjh_mx')                     
   }
   var rtmxdata = await usequery(`select top 1 *  from shipSRM_scjh_mx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, `新增生产计划成功,生产计划单据号${formdata.djh.value}`)
})

//更新生产计划
router.post('/update_scjh', async function (req, res) {
   let db=new Msdb()
   let { formdata, mxdata, storage, delidarr } = req.body
   let updatobj = await update_table_byid(formdata, "shipSRM_scjh")
   await saveczlog_util(updatobj.updatesql, storage, '', '修改生产计划', '修改', 'shipSRM_scjh')
   formdata = updatobj.data;
   var mainid = updatobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      var ifcz = await checkbysql(`select * from shipSRM_scjh_mx where id='${mxitem.id.value}'`)
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = {}
      if (!ifcz) {
         rtmxobj = await insert_table(mxitem, "shipSRM_scjh_mx")
      } else {
         rtmxobj = await update_table_byid(mxitem, "shipSRM_scjh_mx")
      }
   }

   //删除delidarr
   await db.update(`delete from shipSRM_scjh_mx where id in (${delidarr.map(item => `'${item}'`).join(',')}) `)
   var rtmxdata = await usequery(`select *  from shipSRM_scjh_mx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, "更新生产计划成功")
})

//  删除生产计划
router.post('/delete_scjh', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `delete from shipSRM_scjh where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await db.update(`delete from shipSRM_scjh_mx where mainid in (${selectedRows.map(item => `'${item.id}'`).join(',')})`)
   await saveczlog_util(delsql, storage, '', '删除生产计划', '删除', 'shipSRM_scjh')
   res_util(res, {}, true, "删除成功")
})

//查询生产报工单列表
router.post('/bgd_list', async function (req, res) {
   var rtdata = await usequery(`select * from shipSRM_bgd order by create_time desc`)
   res_util(res, rtdata, true, "查询成功")
})

//查询生产报工单列表通过id
router.post('/query_bgd_byid', async function (req, res) {
   var id = req.body.id
   var formdata = await usequery(`select *  from shipSRM_bgd where id ='${id}'`)
   var mxdata = await usequery(`select *  from shipSRM_bgd_mx where mainid ='${id}'`)
   res_util(res, { formdata:formdata[0], mxdata }, true, "查询成功")
})

//新增生产报工单
router.post('/add_bgd', async function (req, res) {
   let { formdata, mxdata, storage } = req.body
   let xlm = await getxl_util_free("seq_bgd")
   formdata.djh.value="BGD"+xlm
   var insrtobj = await insert_table(formdata, "shipSRM_bgd")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增生产报工单', '新增', 'shipSRM_bgd')
   formdata = insrtobj.data;
   var mainid = insrtobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = await insert_table(mxitem, "shipSRM_bgd_mx")
      await saveczlog_util(rtmxobj.insertsql, storage, '', '新增生产报工单明细', '新增', 'shipSRM_bgd_mx')                     
   }
   var rtmxdata = await usequery(`select top 1 *  from shipSRM_bgd_mx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, `新增生产报工单成功,生产报工单单据号${formdata.djh.value}`)
})

//更新生产报工单
router.post('/update_bgd', async function (req, res) {
   let db=new Msdb()
   let { formdata, mxdata, storage, delidarr } = req.body
   let updatobj = await update_table_byid(formdata, "shipSRM_bgd")
   await saveczlog_util(updatobj.updatesql, storage, '', '修改生产报工单', '修改', 'shipSRM_bgd')
   formdata = updatobj.data;
   var mainid = updatobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      var ifcz = await checkbysql(`select * from shipSRM_bgd_mx where id='${mxitem.id.value}'`)
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = {}
      if (!ifcz) {
         rtmxobj = await insert_table(mxitem, "shipSRM_bgd_mx")
      } else {
         rtmxobj = await update_table_byid(mxitem, "shipSRM_bgd_mx")
      }
   }

   //删除delidarr
   await db.update(`delete from shipSRM_bgd_mx where id in (${delidarr.map(item => `'${item}'`).join(',')}) `)
   var rtmxdata = await usequery(`select *  from shipSRM_bgd_mx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, "更新生产报工单成功")
})

//  删除生产报工单
router.post('/delete_bgd', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `delete from shipSRM_bgd where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await db.update(`delete from shipSRM_bgd_mx where mainid in (${selectedRows.map(item => `'${item.id}'`).join(',')})`)
   await saveczlog_util(delsql, storage, '', '删除生产报工单', '删除', 'shipSRM_bgd')
   res_util(res, {}, true, "删除成功")
})

//  新增报工单移动
router.post('/add_bgd_mx', async function (req, res) {
   let { mxdata, storage, formdata,zsl } = req.body
   var db = new Msdb()
   var id = await getxl_util()
   var xlm = await getxl_util_free("seq_bgd")
   var djh = "BGD" + xlm
   let sql = `INSERT into shipSRM_bgd (id,bgsj,bgr,bgzsl,sscj,djh,create_time,create_by,update_time,update_by,bz)VALUES
   ('${id}','${formdata.bgsj.value}','${storage.ygxm}',${zsl},'${formdata.sscj.value}','${djh}'
   ,getdate(),'${storage.ygxm}',getdate(),'${storage.ygxm}','${formdata.bz.value}')`
   await db.update(sql)
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      var idmx = await getxl_util()
      var sqlString = `insert into shipSRM_bgd_mx(id,mainid,create_time,create_by,update_time,update_by,wlbm,wlmc,ggxh,
      dw,bgsl,bbh,gx,gylx)VALUES ('${idmx}','${id}',getdate(),'${storage.ygxm}',getdate(),'${storage.ygxm}','${mxitem.wlbm}',
      '${mxitem.wlmc}','${mxitem.ggxh}','${mxitem.dw}',${mxitem.bgsl},'${mxitem.bbh}','${mxitem.gx}','${mxitem.gylx}')`
         await db.update(sqlString)
   }
   res_util(res, {}, true, "新增成功")
})


//查询生产计划列表
router.post('/wwcjh_mx_list', async function (req, res) {
   let mainid=req.body.mainid
   var rtdata = await usequery(`select * from shipSRM_wwcjhgzb_mx where mainid='${mainid}' order by create_time desc`)
   res_util(res, rtdata, true, "查询成功")
})
//查询未完成计划列表
router.post('/wwcjh_list', async function (req, res) {
   var rtdata = await usequery(`select * from shipSRM_wwcjhgzb order by create_time desc`)
   res_util(res, rtdata, true, "查询成功")
})

//查询未完成计划列表通过id
router.post('/query_wwcjh_byid', async function (req, res) {
   var id = req.body.id
   var formdata = await usequery(`select *  from shipSRM_wwcjhgzb where id ='${id}'`)
   var mxdata = await usequery(`select *  from shipSRM_wwcjhgzb_mx where mainid ='${id}'`)
   res_util(res, { formdata:formdata[0], mxdata }, true, "查询成功")
})

//新增未完成计划
router.post('/add_wwcjh', async function (req, res) {
   let { formdata, mxdata, storage } = req.body
   let xlm = await getxl_util_free("seq_wwcjh")
   formdata.djh.value="WWCJH"+xlm
   var insrtobj = await insert_table(formdata, "shipSRM_wwcjhgzb")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增未完成计划', '新增', 'shipSRM_wwcjhgzb')
   formdata = insrtobj.data;
   var mainid = insrtobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = await insert_table(mxitem, "shipSRM_wwcjhgzb_mx")
      await saveczlog_util(rtmxobj.insertsql, storage, '', '新增未完成计划明细', '新增', 'shipSRM_wwcjhgzb_mx')                     
   }
   var rtmxdata = await usequery(`select top 1 *  from shipSRM_wwcjhgzb_mx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, `新增未完成计划成功,未完成计划单据号${formdata.djh.value}`)
})

//更新未完成计划
router.post('/update_wwcjh', async function (req, res) {
   let db=new Msdb()
   let { formdata, mxdata, storage, delidarr } = req.body
   let updatobj = await update_table_byid(formdata, "shipSRM_wwcjhgzb")
   await saveczlog_util(updatobj.updatesql, storage, '', '修改未完成计划', '修改', 'shipSRM_wwcjhgzb')
   formdata = updatobj.data;
   var mainid = updatobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      var ifcz = await checkbysql(`select * from shipSRM_wwcjhgzb_mx where id='${mxitem.id.value}'`)
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = {}
      if (!ifcz) {
         rtmxobj = await insert_table(mxitem, "shipSRM_wwcjhgzb_mx")
      } else {
         rtmxobj = await update_table_byid(mxitem, "shipSRM_wwcjhgzb_mx")
      }
   }

   //删除delidarr
   await db.update(`delete from shipSRM_wwcjhgzb_mx where id in (${delidarr.map(item => `'${item}'`).join(',')}) `)
   var rtmxdata = await usequery(`select *  from shipSRM_wwcjhgzb_mx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, "更新未完成计划成功")
})

//  删除未完成计划
router.post('/delete_wwcjh', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db=new Msdb()
   const delsql = `delete from shipSRM_wwcjhgzb where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await db.update(`delete from shipSRM_wwcjhgzb_mx where mainid in (${selectedRows.map(item => `'${item.id}'`).join(',')})`)
   await saveczlog_util(delsql, storage, '', '删除未完成计划', '删除', 'shipSRM_wwcjhgzb')
   res_util(res, {}, true, "删除成功")
})


module.exports = router; 